Imports System.Data.SqlClient
Imports System.Configuration
Imports System.IO

Public Class Scanner
    Private moEventLog As EventLog

    Public Sub New()
        moEventLog = New EventLog(logname:="Application", machinename:=".", source:="SQLAutoDocLib")
    End Sub

    Public Sub Immediate_ScanAll()
        Dim oFactory As New Server_Factory
        Dim oList As ArrayList = oFactory.AllActiveServers

        For Each oServer As Server In oList
            Immediate_ScanServer(oServer.ID)
        Next
    End Sub

    Public Sub Immediate_ScanServer(ByVal lServerID As Long)
        '---Scan this server for databases
        Dim oFactory As New Database_Factory

        For Each oDatabase As Database In oFactory.GetAllDatabasesForServer(lServerID)
            Dim oScanner As New SQLAutoDocLib.Scanner
            oScanner.Immediate_ScanDatabase(oDatabase.ID)
        Next
    End Sub

    Public Sub Immediate_ScanDatabase(ByVal lDatabaseID As Long)
        Dim oDatabase As New SQLAutoDocLib.Database(lDatabaseID)
        oDatabase.LoadWorkingRev()

        Dim oSession As New SQLAutoDocLib.Session
        oSession.DatabaseID = lDatabaseID
        oSession.Save()

        Dim oDBScanner As DBScanDatabase

        oDBScanner = New DBScanDatabase(oDatabase, oSession.ID, ScanSchedule.Type.SchemaScan)
        oDBScanner.Scan()

        oDBScanner = New DBScanDatabase(oDatabase, oSession.ID, ScanSchedule.Type.SizeScan)
        oDBScanner.Scan()

        oDBScanner = New DBScanDatabase(oDatabase, oSession.ID, ScanSchedule.Type.ConnectionScan)
        oDBScanner.Scan()

        oDBScanner = New DBScanDatabase(oDatabase, oSession.ID, ScanSchedule.Type.BlockScan)
        oDBScanner.Scan()

        oDBScanner = New DBScanDatabase(oDatabase, oSession.ID, ScanSchedule.Type.WaitStateScan)
        oDBScanner.Scan()
    End Sub

    Public Sub Immediate_ScanDatabase( _
                        ByVal lDatabaseID As Long, _
                        ByVal lTypeID As ScanSchedule.Type)

        Dim oDatabase As New SQLAutoDocLib.Database(lDatabaseID)
        oDatabase.LoadWorkingRev()

        Dim oSession As New SQLAutoDocLib.Session
        oSession.DatabaseID = lDatabaseID
        oSession.Save()

        Dim oDBScanner As DBScanDatabase

        oDBScanner = New DBScanDatabase(oDatabase, oSession.ID, lTypeID)
        oDBScanner.Scan()
    End Sub

    Public Sub Queue_ScanDatabase()
        Dim sDatabaseName As String = "Database Undefined"

        Try
            Dim oFactory As New ScanQueue_Factory
            Dim oQueue As ScanQueue = oFactory.GetNextQueuedRequest

            If Not oQueue Is Nothing Then
                Dim oDatabase As New SQLAutoDocLib.Database(oQueue.DatabaseID)
                oDatabase.LoadWorkingRev()
                sDatabaseName = oDatabase.Name

                Dim oDBScanner As New DBScanDatabase(oDatabase, oQueue.SessionID, oQueue.ScanTypeID)

                oDBScanner.Scan()

                Dim oScanSchedule_Factory As New SQLAutoDocLib.ScanSchedule_Factory
                If oScanSchedule_Factory.GetReportRequestedForDatabase(oDatabase.ID, oQueue.ScanTypeID) Then
                    SendScanReport(oDatabase.ServerID, oDatabase.ID, oQueue.ScanTypeID)
                End If
            End If

            CheckAndScheduleAll()

            SendNextScheduledServerReport()

        Catch oEX As Exception
            Dim oEventFactory As New ErrorLog.Log
            oEventFactory.LogMessage(oEX)

            moEventLog.WriteEntry("Fatal error in SQLAutoDoc: " & oEX.GetType.ToString & ".", System.Diagnostics.EventLogEntryType.Warning)

            Dim oMessage As New SADSendMail
            Dim sResult As String = oMessage.SendMessage( _
                                        "Fatal error in SQLAutoDoc: " & oEX.Message, _
                                        sDatabaseName & vbCrLf & oEX.Message & vbCrLf & oEX.GetType.ToString & vbCrLf & oEX.Source & vbCrLf & oEX.StackTrace, _
                                        "", _
                                        "")

            If sResult > "" Then
                moEventLog.WriteEntry("Send Email Failed: " & sResult & ".", System.Diagnostics.EventLogEntryType.Warning)
            End If
        End Try
    End Sub

    Public Function VerifyConnection(ByVal sConnection As String) As String
        Dim sResult As String = ""
        Try
            Dim oServerInfo As New DBServer_Info
            sResult = oServerInfo.DatabaseName(sConnection)

        Catch oEX As Exception
            sResult = ""
        End Try

        Return sResult
    End Function

    Private Sub CheckAndScheduleAll()
        Dim oServerFactory As New Server_Factory

        For Each oServer As Server In oServerFactory.AllActiveServers
            Try
                Dim oDBFactory As New Database_Factory
                For Each oDatabase As Database In oDBFactory.GetAllDatabasesForServer(oServer.ID)
                    Dim oDBScanner As New DBScanDatabase(oDatabase)
                    Dim lSessionID As Long = SQLAutoDocLib.Database.NODATA

                    oDBScanner.CheckAndSchedule(oDatabase, ScanSchedule.Type.SchemaScan, lSessionID)
                    oDBScanner.CheckAndSchedule(oDatabase, ScanSchedule.Type.SizeScan, lSessionID)
                    oDBScanner.CheckAndSchedule(oDatabase, ScanSchedule.Type.BlockScan, lSessionID)
                    oDBScanner.CheckAndSchedule(oDatabase, ScanSchedule.Type.ConnectionScan, lSessionID)
                    oDBScanner.CheckAndSchedule(oDatabase, ScanSchedule.Type.WaitStateScan, lSessionID)
                Next

            Catch oEx As Exception
                Dim oLog As New ErrorLog.Log
                oLog.LogMessage(oEx)
            End Try
        Next
    End Sub

    Public ReadOnly Property ServiceInterval() As Long
        Get
            Dim oFactory As New SQLAutoDocLib.Config_Factory
            Dim oConfig As Config = oFactory.GetConfigFromName("SERVICE_INTERVAL")

            Return oConfig.Value
        End Get
    End Property

#Region "Email Reports"
    Private Sub SendNextScheduledServerReport()
        Dim oReportFactory As New ReportSchedule_Factory
        Dim oReportSchedule As ReportSchedule = oReportFactory.SendNextScheduledReport

        If Not oReportSchedule Is Nothing Then
            oReportSchedule.LastReportCompileDate = Now
            oReportSchedule.Save()

            Dim oFactory As New SQLAutoDocLib.Config_Factory
            Dim oConfig As Config = oFactory.GetConfigFromName("ROOT_URL")

            Dim sRootURL As String = oConfig.Value

            Dim sSubject As String = ""
            Dim sMessage As String = ""

            If oReportSchedule.ServerID <> Base.NODATA Then
                Dim oServer As New SQLAutoDocLib.Server(oReportSchedule.ServerID)
                oServer.Load()
                sSubject = "SQLAutoDoc report for server " & oServer.Name

                Dim oSum As New SUM_Server(sRootURL, oReportSchedule.ServerID)
                sMessage = oSum.Summerize

            ElseIf oReportSchedule.DatabaseID <> Base.NODATA Then
                Dim oDatabase As New SQLAutoDocLib.Database(oReportSchedule.DatabaseID)
                oDatabase.Load()
                sSubject = "SQLAutoDoc report for database " & oDatabase.Name

                Dim oSum As New SUM_Database(sRootURL, oReportSchedule.ServerID)
                sMessage = oSum.Summerize

            Else
                sSubject = "SQLAutoDoc report for all known servers"

                Dim oSum As New SUM_System(sRootURL)
                sMessage = oSum.Summerize
            End If

            Dim oMail As New SADSendMail
            If oMail.SendMessage( _
                    sSubject, _
                    sMessage, _
                    "", _
                    oReportSchedule.RecipientEmailAddresses) = "" Then

                oReportSchedule.LastReportSendDate = Now
                oReportSchedule.Save()
            End If
        End If
    End Sub

    Private Sub SendScanReport( _
                        ByVal lServerID As Long, _
                        ByVal lDatabaseID As Long, _
                        ByVal lTypeID As ScanSchedule.Type)

        Select Case lTypeID
            Case ScanSchedule.Type.BlockScan
                SendBlockScanReport(lServerID, lDatabaseID)

            Case ScanSchedule.Type.ConnectionScan
                SendConnectionScanReport(lServerID, lDatabaseID)

            Case ScanSchedule.Type.SchemaScan
                SendSchemaScanReport(lServerID, lDatabaseID)

            Case ScanSchedule.Type.SizeScan
                SendSizeScanReport(lServerID, lDatabaseID)

            Case ScanSchedule.Type.WaitStateScan
                SendWaitStateScanReport(lServerID, lDatabaseID)
        End Select

    End Sub

    Private Sub SendSizeScanReport( _
                    ByVal lServerID As Long, _
                    ByVal lDatabaseID As Long)

        Dim oReportFactory As New SQLAutoDocLib.ReportSchedule_Factory
        Dim oReportSchedule As SQLAutoDocLib.ReportSchedule = oReportFactory.GetReportForDatabase(lDatabaseID)
        If oReportSchedule Is Nothing Then oReportSchedule = oReportFactory.GetReportForServer(lServerID)
        If oReportSchedule Is Nothing Then oReportSchedule = oReportFactory.GetReportForSystem

        Dim oFactory As New SQLAutoDocLib.Config_Factory
        Dim oConfig As Config = oFactory.GetConfigFromName("ROOT_URL")

        Dim sRootURL As String = oConfig.Value

        Dim sSubject As String = ""
        Dim sMessage As String = ""

        Dim oDatabase As New SQLAutoDocLib.Database(lDatabaseID)
        oDatabase.Load()
        sSubject = "SQLAutoDoc Size report for database " & oDatabase.Name

        Dim oSum As New SUM_Size(sRootURL, lDatabaseID)
        sMessage = oSum.Summerize

        If oSum.DataExists Then
            '---Make chart
            Dim sFileName As String = ""
            Dim sPath As String = ConfigurationManager.AppSettings("temppath")
            If Directory.Exists(sPath) Then
                Dim oChartFactory As New Rpt_Charts
                sFileName = sPath & oChartFactory.MakeDatabaseSizeChart(lDatabaseID, sPath)
            End If

            Dim oMail As New SADSendMail
            oMail.SendMessage( _
                   sSubject, _
                   sMessage, _
                   "", _
                   oReportSchedule.RecipientEmailAddresses, _
                   sAttachmentPath:=sFileName)
        End If
    End Sub

    Private Sub SendBlockScanReport( _
                    ByVal lServerID As Long, _
                    ByVal lDatabaseID As Long)

        Dim oScanFactory As New SQLAutoDocLib.ScanSchedule_Factory
        Dim oScan As SQLAutoDocLib.ScanSchedule = oScanFactory.GetScheduleForDatabase(lDatabaseID, ScanSchedule.Type.BlockScan)

        Dim oBlockFactory As New BlockLog_Factory
        Dim oDT As DataTable = oBlockFactory.AllDetailForPeriod(Now.AddMinutes(-1.5 * oScan.ScanTypeMinutes), Now, lDatabaseID)
        If oDT.Rows.Count > 0 Then
            Dim oReportFactory As New SQLAutoDocLib.ReportSchedule_Factory
            Dim oReportSchedule As SQLAutoDocLib.ReportSchedule = oReportFactory.GetReportForDatabase(lDatabaseID)
            If oReportSchedule Is Nothing Then oReportSchedule = oReportFactory.GetReportForServer(lServerID)
            If oReportSchedule Is Nothing Then oReportSchedule = oReportFactory.GetReportForSystem

            Dim oFactory As New SQLAutoDocLib.Config_Factory
            Dim oConfig As Config = oFactory.GetConfigFromName("ROOT_URL")

            Dim sRootURL As String = oConfig.Value

            Dim sSubject As String = ""
            Dim sMessage As String = ""

            Dim oDatabase As New SQLAutoDocLib.Database(lDatabaseID)
            oDatabase.Load()
            sSubject = "SQLAutoDoc Block report for database " & oDatabase.Name

            Dim oSum As New SUM_Blocks(sRootURL, lDatabaseID)
            sMessage = oSum.Summerize

            If oSum.DataExists Then
                Dim oMail As New SADSendMail
                oMail.SendMessage( _
                       sSubject, _
                       sMessage, _
                       "", _
                       oReportSchedule.RecipientEmailAddresses)
            End If
        End If
    End Sub

    Private Sub SendConnectionScanReport( _
                        ByVal lServerID As Long, _
                        ByVal lDatabaseID As Long)

        Dim oScanFactory As New SQLAutoDocLib.ScanSchedule_Factory
        Dim oScan As SQLAutoDocLib.ScanSchedule = oScanFactory.GetScheduleForDatabase(lDatabaseID, ScanSchedule.Type.BlockScan)

        Dim oConnectFactory As New ConnectLog_Factory
        Dim oDT As DataTable = oConnectFactory.ConnectionsInPeriod(lDatabaseID, Now.AddMinutes(-1.5 * oScan.ScanTypeMinutes), Now)
        If oDT.Rows.Count > 0 Then

            Dim oReportFactory As New SQLAutoDocLib.ReportSchedule_Factory
            Dim oReportSchedule As SQLAutoDocLib.ReportSchedule = oReportFactory.GetReportForDatabase(lDatabaseID)
            If oReportSchedule Is Nothing Then oReportSchedule = oReportFactory.GetReportForServer(lServerID)
            If oReportSchedule Is Nothing Then oReportSchedule = oReportFactory.GetReportForSystem

            Dim oFactory As New SQLAutoDocLib.Config_Factory
            Dim oConfig As Config = oFactory.GetConfigFromName("ROOT_URL")

            Dim sRootURL As String = oConfig.Value

            Dim sSubject As String = ""
            Dim sMessage As String = ""

            Dim oDatabase As New SQLAutoDocLib.Database(lDatabaseID)
            oDatabase.Load()
            sSubject = "SQLAutoDoc Connection report for database " & oDatabase.Name

            Dim oSum As New SUM_Connect(sRootURL, lDatabaseID)
            sMessage = oSum.Summerize

            If oSum.DataExists Then
                '---Make chart
                Dim sFileName As String = ""
                Dim sPath As String = ConfigurationManager.AppSettings("temppath")
                If Directory.Exists(sPath) Then
                    Dim oChartFactory As New Rpt_Charts
                    sFileName = sPath & oChartFactory.MakeDayDatabaseConnectionChart(lDatabaseID, Now.ToShortDateString, sPath)
                End If

                Dim oMail As New SADSendMail
                oMail.SendMessage( _
                       sSubject, _
                       sMessage, _
                       "", _
                       oReportSchedule.RecipientEmailAddresses, _
                       sAttachmentPath:=sFileName)
            End If
        End If
    End Sub

    Private Sub SendWaitStateScanReport( _
                        ByVal lServerID As Long, _
                        ByVal lDatabaseID As Long)

        Dim oReportFactory As New SQLAutoDocLib.ReportSchedule_Factory
        Dim oReportSchedule As SQLAutoDocLib.ReportSchedule = oReportFactory.GetReportForDatabase(lDatabaseID)
        If oReportSchedule Is Nothing Then oReportSchedule = oReportFactory.GetReportForServer(lServerID)
        If oReportSchedule Is Nothing Then oReportSchedule = oReportFactory.GetReportForSystem

        Dim oFactory As New SQLAutoDocLib.Config_Factory
        Dim oConfig As Config = oFactory.GetConfigFromName("ROOT_URL")

        Dim sRootURL As String = oConfig.Value

        Dim sSubject As String = ""
        Dim sMessage As String = ""

        Dim oDatabase As New SQLAutoDocLib.Database(lDatabaseID)
        oDatabase.Load()
        sSubject = "SQLAutoDoc WaitState report for database " & oDatabase.Name

        Dim oSum As New SUM_WaitStates(sRootURL, lDatabaseID)
        sMessage = oSum.Summerize

        If oSum.DataExists Then
            Dim oMail As New SADSendMail
            oMail.SendMessage( _
                   sSubject, _
                   sMessage, _
                   "", _
                   oReportSchedule.RecipientEmailAddresses)
        End If
    End Sub

    Private Sub SendSchemaScanReport( _
                    ByVal lServerID As Long, _
                    ByVal lDatabaseID As Long)

        Dim oReportFactory As New SQLAutoDocLib.ReportSchedule_Factory
        Dim oReportSchedule As SQLAutoDocLib.ReportSchedule = oReportFactory.GetReportForDatabase(lDatabaseID)
        If oReportSchedule Is Nothing Then oReportSchedule = oReportFactory.GetReportForServer(lServerID)
        If oReportSchedule Is Nothing Then oReportSchedule = oReportFactory.GetReportForSystem

        Dim oFactory As New SQLAutoDocLib.Config_Factory
        Dim oConfig As Config = oFactory.GetConfigFromName("ROOT_URL")

        Dim sRootURL As String = oConfig.Value

        Dim sSubject As String = ""
        Dim sMessage As String = ""

        Dim oDatabase As New SQLAutoDocLib.Database(lDatabaseID)
        oDatabase.Load()
        sSubject = "SQLAutoDoc Schema report for database " & oDatabase.Name

        Dim oSum As New SUM_Schema(sRootURL, lDatabaseID)
        sMessage = oSum.Summerize

        If oSum.DataExists Then
            Dim oMail As New SADSendMail
            oMail.SendMessage( _
                   sSubject, _
                   sMessage, _
                   "", _
                   oReportSchedule.RecipientEmailAddresses)
        End If
    End Sub
#End Region

    Public Function Version() As String
        Return System.Diagnostics.FileVersionInfo.GetVersionInfo(System.Reflection.Assembly.GetExecutingAssembly.Location).ProductName & " " & _
               System.Diagnostics.FileVersionInfo.GetVersionInfo(System.Reflection.Assembly.GetExecutingAssembly.Location).FileVersion
    End Function

    Public Sub Test()
        SendConnectionScanReport(lServerID:=23, lDatabaseID:=103)
    End Sub
End Class
